package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Product;
import com.ysd.util.DBManager;

public class ProductDaoImpl {
	
	/**
	 * 根据产品分类id查询对应的产品信息
	 * @param cateId
	 * @return
	 */
	public List<Product> getProductsByCateId(int cateId){//1
		List<Product> pList = new ArrayList<Product>();
		
		Connection conn = DBManager.getConnection();
		
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from product where categoryId=" + cateId);
			while (rs.next()) {
				String pId = rs.getString("ProductId");
				String pName = rs.getString("ProductName");
				String imgs = rs.getString("Imgs");
				Product p = new Product(pId, pName, imgs);
				pList.add(p);
			}
			return pList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 根据产品id查询产品详情
	 */
	public Product getProductById(String pId){
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Product product = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from product where productId='"+pId+"'");
			while (rs.next()) {
				String productId = rs.getString("ProductId");
				String productName = rs.getString("ProductName");
				String productMaterial = rs.getString("ProductMaterial");
				int productPrice = rs.getInt("ProductPrice");
				int markPrice = rs.getInt("MarkPrice");
				String brand = rs.getString("Brand");
				String isPackage = rs.getString("IsPackage");
				String isOrder = rs.getString("IsOrder");
				String color = rs.getString("Color");
				String element = rs.getString("Element");
				String style = rs.getString("Style");
				String stylePositioning = rs.getString("StylePositioning");
				String imgs = rs.getString("Imgs");
				int categoryId = rs.getInt("CategoryId");
				product = new Product(productId, productName, productMaterial, productPrice, markPrice, brand, isPackage, isOrder, color, element, style, stylePositioning, imgs, categoryId);
			}
			return product;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 根据产品名称模糊查询产品信息
	 */
	public List<Product> getProductsByLike(String pName){
		List<Product> pList = new ArrayList<Product>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from product where productName like '%"+pName+"%'");
			while (rs.next()) {
				String productId = rs.getString("ProductId");
				String productName = rs.getString("ProductName");
				String imgs = rs.getString("Imgs");
				Product p = new Product(productId, productName, imgs);
				pList.add(p);
			}
			return pList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 多条件查询产品详情
	 * 为什么返回List? 因为在查询时可能查询到多条数据
	 */
	public List<Product> getProductsByWhere(Product p){
		List<Product> pList = new ArrayList<Product>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			// 因为多条件查询条件有可能填写, 也有有可能不写, 那么sql语句不能固定写死, 需要通过条件判断拼接sql语句
			String sql = "select p.*,pc.ProductCategoryName from product p, productcategory pc where 1=1"; //where 1=1是一个永真条件(永远为true), 目的是为了解决拼接where
			// 非空判定
			if(p.getProductId()!=null && !p.getProductId().equals("")) {
				sql += " and ProductId like '%"+p.getProductId()+"%'";
			}
			if(p.getProductName()!=null && !p.getProductName().equals("")) {
				sql += " and ProductName like '%"+p.getProductName()+"%'";
			}
			if(p.getProductMaterial()!=null && !p.getProductMaterial().equals("")) {
				sql += " and ProductMaterial='"+p.getProductMaterial()+"'";
			}
			if(p.getBeginProductPrice() > 0) {
				sql += " and ProductPrice>" + p.getBeginProductPrice();
			}
			if(p.getEndProductPrice() > 0) {
				sql += " and ProductPrice<" + p.getEndProductPrice();
			}
			if(p.getBrand()!=null && !p.getBrand().equals("")) {
				sql += " and Brand='"+p.getBrand()+"'";
			}
			if(p.getCategoryId() > 0) {
				sql += " and CategoryId=" + p.getCategoryId();
			}
			sql += " and p.CategoryId=pc.ProductCategoryId";
			
			System.out.println(sql); // 打印拼好的sql语句, 方便以后在控制台观看sql语句是否有误
			
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String productId = rs.getString("ProductId");
				String productName = rs.getString("ProductName");
				String productMaterial = rs.getString("ProductMaterial");
				int productPrice = rs.getInt("ProductPrice");
				int markPrice = rs.getInt("MarkPrice");
				String brand = rs.getString("Brand");
				String isPackage = rs.getString("IsPackage");
				String isOrder = rs.getString("IsOrder");
				String color = rs.getString("Color");
				String element = rs.getString("Element");
				String style = rs.getString("Style");
				String stylePositioning = rs.getString("StylePositioning");
				String imgs = rs.getString("Imgs");
				int categoryId = rs.getInt("CategoryId");
				String productCategoryName = rs.getString("ProductCategoryName");
				
				Product product = new Product(productId, productName, productMaterial, productPrice, 0, 0, markPrice, brand, isPackage, isOrder, color, element, style, stylePositioning, imgs, categoryId, productCategoryName);
				pList.add(product); //添加到集合中
			}
			return pList;//返回得到的集合
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
}
